/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.etop.dao;

import com.etop.beans.Borrow;
import com.etop.beans.Goods;
import com.etop.dto.UserGoodsDTO;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

/**
 *
 * @author cjh(358319110@qq.com)
 */
public class GoodsDao {

    private SessionFactory sessionFactory;

    public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    protected Session getSession() {
        return getSessionFactory().getCurrentSession();
    }

    public void addGoods(Goods goods) {
        this.getSession().save(goods);
    }

    public Goods loadGoodsById(int gid) {
        return (Goods) this.getSession().createQuery("from Goods where id = ?").setParameter(0, gid).uniqueResult();
    }

    public void upDate(Goods goods) {
        this.getSession().update(goods);
    }

    public void delete(Goods goods) {
        String hql = "delete from Borrow where goods.id = :gid";
        this.getSession().createQuery(hql).setParameter("gid", goods.getId()).executeUpdate();
        this.getSession().delete(goods);
    }

    public List<Goods> listGoods() {
        String hql = "from Goods";
        List<Goods> goodsList = this.getSession().createQuery(hql).list();
        return goodsList;
    }

    public List<UserGoodsDTO> listUserGoodsDTO(String key) {
        List<Object[]> resuts = this.getSession()
                .createSQLQuery("select * "
                        + "FROM (select t3.`name` as goodsName, t1.isReturn as isReturn,t2.name as userName,t1.lend_time as lendDate,"
                        + "t1.re_time as returnDate,t2.tel as userPhone,"
                        + "t2.email as userEmail,t2.address as userAddress,t3.id as gid,t1.id as bid,t3.description as description "
                        + "from labregistration.borrow t1 join labregistration.user t2 on(t1.u_id=t2.id) right join labregistration.Goods t3 on (t3.id=t1.g_id) "
                        + "where t3.name like ? "
                        + "ORDER BY t1.lend_time desc) eTop GROUP BY gid;")
                .setParameter(0, '%' + key + '%')
                .list();
        List<UserGoodsDTO> listUserGoodsDTO = new ArrayList<>();
        for (Object[] os : resuts) {
            int bid, isReturn;
            if (null == os[9]) {
                bid = -1;
            } else {
                bid = (int) os[9];
            }
            if (null == os[1]) {
                isReturn = 1;
            } else {
                isReturn = (int) os[1];
            }
            listUserGoodsDTO.add(new UserGoodsDTO((String) os[0],
                    isReturn, (String) os[2], (Date) os[3], (Date) os[4],
                    (String) os[5], (String) os[6], (String) os[7],
                    (int) os[8], bid, (String) os[10]));
        }
        return listUserGoodsDTO;
    }

    public Borrow loadBorrow(int userid, String goodsName) {

        String checkSql = "from Borrow where user.id=? and goods.name=? order by id desc";
        List<Borrow> bL = this.getSession().createQuery(checkSql)
                .setParameter(0, userid).setParameter(1, goodsName).list();

        if (bL.size() != 0) {
            Borrow u = bL.get(0);
            return u;
        }

        return null;
    }

    public Borrow loadBorrowByid(int bid) {

        return (Borrow) this.getSession().createQuery("from Borrow where id = ?").setParameter(0, bid).uniqueResult();
    }

    public void updateBorrow(Borrow borrow) {
        this.getSession().update(borrow);

    }
}
